CREATE OR REPLACE PROCEDURE load_product_image (p_dir IN VARCHAR2) 
IS
  v_file           BFILE;
  v_filename       VARCHAR2(40);
  v_rec_number     NUMBER;
  v_file_exists    BOOLEAN;
  CURSOR product_csr IS
    SELECT product_id
    FROM product_information
    WHERE category_id = 12
    FOR UPDATE;
BEGIN
  DBMS_OUTPUT.PUT_LINE('LOADING LOCATORS TO IMAGES...');
  FOR rec IN product_csr
  LOOP
    v_filename := rec.product_id || '.gif';
    v_file := BFILENAME(p_dir, v_filename);
    v_file_exists := (DBMS_LOB.FILEEXISTS(v_file) = 1);
    IF v_file_exists THEN
     DBMS_LOB.FILEOPEN(v_file);
     UPDATE product_information
       SET picture = v_file
       WHERE CURRENT OF product_csr;
     DBMS_OUTPUT.PUT_LINE('Set Locator to file: '|| v_filename ||
       ' Size: ' || DBMS_LOB.GETLENGTH(v_file));
     DBMS_LOB.FILECLOSE(v_file);
     v_rec_number := product_csr%ROWCOUNT;
    ELSE
     DBMS_OUTPUT.PUT_LINE('File ' || v_filename ||' does not exist');
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('TOTAL FILES UPDATED: ' || v_rec_number);   
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_LOB.FILECLOSE(v_file);
      DBMS_OUTPUT.PUT_LINE('Error: '|| to_char(SQLCODE) || SQLERRM);
END load_product_image;
/
